package model.data.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DatabaseHandler {
	/* Connection to the database */
	private Connection connection;
	
	/* Connects the the database */
	private void connect() throws DatabaseCommunicationException {
		DatabaseConnector connector = new SqlServerConnector();
		this.connection = connector.getConnection();
	}
	
	/* Disconnects from the database */
	private void disconnect() {
		try {
			this.connection.close();
		}
		catch (SQLException sqle) {
			/* ignore */
		}
		finally {
			this.connection = null;
		}
	}
	
	/** Calls a stored procedure that queries for information */
	public List<DataRecord> callQueryProcedure(String name, Object... values) throws Exception {
		List<DataRecord> records = null;
		ResultSet resultSet = null;
		CallableStatement statement = null;
		
		try {
			// Create a connection
			this.connect();
			
			// Initialize the stored procedure
			statement = this.connection.prepareCall(this.getCallName(name));
			
			// Assign parameters to the procedure
			this.assignParameters(statement, values);
			
			// Execute the statement (will return a ResultSet object)
			resultSet = statement.executeQuery();
			
			// Translate the result set to a neutral list object
			records = translateResultSet(resultSet);
			
			// Return the results
			return records;
		}
		catch (SQLException sqle) {
			throw new Exception(sqle.getMessage(), sqle);
		}
		finally {
			try { resultSet.close(); } catch (SQLException sqle) {}
			try { statement.close(); } catch (SQLException sqle) {}
			this.disconnect();
		}
	}
	
	/** Calls a stored procedure that modifies the database (insert, update, delete) */
	public void callUpdateProcedure(String name, Object... values) throws Exception {
		CallableStatement statement = null;
		
		try {
			// Create a connection
			this.connect();
			
			// Initialize the stored procedure
			statement = this.connection.prepareCall(this.getCallName(name));
			
			// Assign parameters to the procedure
			this.assignParameters(statement, values);
			
			// Execute the statement
			statement.executeUpdate();
			this.connection.commit();
		}
		catch (SQLException sqle) {
			throw new Exception(sqle.getMessage(), sqle);
		}
		finally {
			try { statement.close(); } catch (SQLException sqle) {}
			this.disconnect();
		}
	}
	
	/* Formats a procedure name so it can be used as argument for a procedure call */
	private String getCallName(String procedureName) {
		return String.format(String.format("{call %s}", procedureName));
	}
	
	/* Assigns parameter values to stored procedure parameters */
	private void assignParameters(CallableStatement statement, Object... values) throws SQLException {
		ParameterMetaData parameterData; 
		
		// Retrieve parameter details (exposes count and modes)
		parameterData = statement.getParameterMetaData();
		
		// Assign to each parameter the correct value 
		for (int i = 0; i < parameterData.getParameterCount(); i++) {
			
			// Get the parameter mode (in/out/inout/unknown) for the parameter
			int mode = parameterData.getParameterMode(i + 1);
			
			// Depending on the mode we need to take a different action
			switch (mode) {
				case ParameterMetaData.parameterModeIn:
					statement.setObject(i + 1, values[i]);
					break;
				case ParameterMetaData.parameterModeOut:
					// Register out parameter
					break;
				case ParameterMetaData.parameterModeInOut:
					// Well this is getting difficult :-)
					break;
				case ParameterMetaData.parameterModeUnknown:
					// Now I just have no idea
					break;
				default:
					break;
			}
		}
	}
	
	/* Translates a ResultSet object to a list of data records */
	public List<DataRecord> translateResultSet (ResultSet resultSet) throws SQLException {
		List<DataRecord> records;
		ResultSetMetaData resultSetData;

		// Retrieve result set information (exposes column labels)
		resultSetData = resultSet.getMetaData();
		
		// Get the number of columns in the result set */
		int columns = resultSetData.getColumnCount();
		
		// Create list of data records
		records = new ArrayList<DataRecord>();
		
		// Fill the list with result set data
		while (resultSet.next()) {
			
			// Create a new data record
			DataRecord dr = new DataRecord();
			
			// Fill attributes of the data record with result set information
			for (int i = 0; i < columns; i++) {
				String parameter = resultSetData.getColumnLabel(i + 1);
				String value = resultSet.getString(i + 1);
				dr.addColumn(parameter, value);
			}
			records.add(dr);
		}
		
		// Return the result
		return records;
	}
}